﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
namespace Lib.Integration
{
    public class IntegrateAllXML
    {
        public static void IntegrateXMLToSQL(string CateColName, string Table, string sqlConnection, string fPath)
        {
            System.Data.SqlClient.SqlConnection scon = new System.Data.SqlClient.SqlConnection(sqlConnection);
            System.Data.SqlClient.SqlCommand scmd = new System.Data.SqlClient.SqlCommand("SELECT * FROM " + Table, scon);
            System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter(scmd);
            System.Data.SqlClient.SqlCommandBuilder scb = new System.Data.SqlClient.SqlCommandBuilder(sda);
            
            sda.InsertCommand = scb.GetInsertCommand();

            System.Data.DataTable tbl = new System.Data.DataTable();
            sda.FillSchema(tbl, System.Data.SchemaType.Source);
            IntegrateDataFromFiles(CateColName, fPath, ref tbl);
            sda.Update(tbl);
        }

        static void IntegrateDataFromFiles(string categoryColumnName, string fPath, ref System.Data.DataTable tbl)
        {
            System.IO.DirectoryInfo di = new System.IO.DirectoryInfo(fPath);
            if (di.Exists)
            {
                foreach (System.IO.DirectoryInfo d in di.GetDirectories())
                    IntegrateDataFromFiles(categoryColumnName, d.FullName, ref tbl);
                foreach (System.IO.FileInfo f in di.GetFiles())
                {
                    System.Xml.XmlDocument xdoc = new System.Xml.XmlDocument();
                    xdoc.Load(f.FullName);
                    System.Xml.XmlNodeList xlist = xdoc.SelectNodes("/Pages");
                    System.Data.DataRow dr = tbl.NewRow();
                    foreach (System.Xml.XmlNode xnode in xlist)
                    {
                        if (xnode.Name == "Pages")
                            foreach (System.Xml.XmlNode x in xnode)
                            {
                                try
                                {
                                    dr[x.Name] = x.Value;
                                }
                                catch { }
                            }
                        else
                            try
                            {
                                dr[xnode.Name] = xnode.Value;
                            }
                            catch { }
                    }

                    if (!string.IsNullOrEmpty(categoryColumnName))
                        dr[categoryColumnName] = xlist[0]["category"].Value;
                    dr["News_Status"] = 1;
                    tbl.Rows.Add(dr);

                    f.Delete();
                }
            }
        }
    }
}
